<?php
	class MssqlEngine
	{
		/**
		 * connection to the database
		 *
		 * @var mssql link
		 */
		public $connection;
		
		/**
		 * defines whether database connection is established
		 *
		 * @var boolean
		 */
		public $connected;
		
		/**
		 * databasea formats
		 *
		 * @var array
		 */
		public $database_formats = array
		(
			'text_formats' => array('char', 'varchar', 'tinytext','text','mediumtext','longtext'),
			'date_formats' => array('date'),
			'datetime_formats' => array('datetime'),
			'numeric_formats' => array('integer', 'int', 'bigint', 'tinyint','smallint', 'mediumint', 'decimal')
		);
		
		/**
 		* Connects to the database using options in the given database configuration array
 		*
 		* @return boolean $this->connected true if the database could be connected to, otherwise - false
 		*/
		function Connect($db_config)
		{
			$connect = $db_config['connect'];
			$this->connected = false;
			
			Logger::Debug("Connect string started");
			$this->connection = $connect($db_config['host'], $db_config['login'], $db_config['password'], false);
			Logger::Debug("Connect string ended");

			if(!$this->connection) 
			{
				throwError(DB_ERROR, "Can't connect to the MSSQL server");
			}
			else 
			{
				if (mssql_select_db($db_config['database'], $this->connection))
				{
					$this->connected = true;
				}
			}
			return $this->connected;
		}

		/**
 		* Disconnects from the database
 		*
 		* @return boolean true if the database could be disconnected from, otherwise - false
 		*/
		function Disconnect()
		{
			$this->connected = !@mssql_close($this->connection);
			return !$this->connected;
		}
		
		
		//SQL SCHEME FUNCTIONS
		
		/**
		 * Gets DB structure
		 *
		 * @param array $options options
		 * 
		 * @return array
		 */
		public function GetSchema(array $options)
		{
			$where_string = '';

			if (isset($options[database_name])) $where_string .= "AND c.TABLE_CATALOG = '$options[database_name]'";
			if (isset($options[schema_name])) $where_string .= "AND c.TABLE_SCHEMA = '$options[schema_name]'";
			if (isset($options[table_name])) $where_string .= "AND c.TABLE_NAME = '$options[table_name]'";
			if (isset($options[column_name])) $where_string .= "AND c.COLUMN_NAME = '$options[column_name]'";
						
			$result = mssql_query("
				SELECT c.TABLE_CATALOG as database_name, c.TABLE_SCHEMA as schema_name, c.TABLE_NAME as table_name, c.COLUMN_NAME as column_name, 
					   c.ORDINAL_POSITION as position, c.COLUMN_DEFAULT as default_value, c.IS_NULLABLE as is_null, c.DATA_TYPE as data_type, 
					   c.CHARACTER_MAXIMUM_LENGTH as max_length, k.CONSTRAINT_NAME as constraint_name
				FROM INFORMATION_SCHEMA.COLUMNS c LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON
					c.TABLE_CATALOG = k.TABLE_CATALOG AND c.TABLE_SCHEMA = k.TABLE_SCHEMA AND c.TABLE_NAME = k.TABLE_NAME 
					AND c.COLUMN_NAME = k.COLUMN_NAME
				WHERE 1=1 $where_string
				ORDER BY database_name, schema_name, table_name, position
				");
	
			$objects = array();
			while ($object = mssql_fetch_assoc($result))
			{
				$this->ParseConstraints(&$object);
				$object[original_data_type] = $object[data_type];
				$object[data_type] = $this->DataTypeMap($object[data_type]);
				$object[default_value] = ereg_replace("^\({2}|\){2}$", "", $object[default_value]);
				$objects[] = $object;
				$this->AddForeignFields($object, $options, &$objects);
			}
			mssql_next_result($result);
			mssql_free_result($result);
			$this->UseCurrentDatabase();
			return $objects;
		}
		
		/**
		 * Adds a field from the foreign table
		 *
		 * @param array $object current element
		 * @param array $options options
		 * @param array $objects elements' array
		 */
		public function AddForeignFields($object, $options = array(), &$objects)
		{
			if (empty($object[foreign_table])) return;
			
			$where_string = '';

			if (isset($options[database_name])) $where_string .= "AND c.TABLE_CATALOG = '$options[schema_name]'";
			if (isset($options[schema_name])) $where_string .= "AND c.TABLE_SCHEMA = '$options[database_name]'";
			
			$result = mssql_query("
				SELECT c.TABLE_CATALOG as database_name, c.TABLE_SCHEMA as schema_name, c.TABLE_NAME as table_name, c.COLUMN_NAME as column_name, 
					   c.ORDINAL_POSITION as position, c.COLUMN_DEFAULT as default_value, c.IS_NULLABLE as is_null, c.DATA_TYPE as data_type, 
					   c.CHARACTER_MAXIMUM_LENGTH as max_length, k.CONSTRAINT_NAME as constraint_name
				FROM INFORMATION_SCHEMA.COLUMNS c LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON
					c.TABLE_CATALOG = k.TABLE_CATALOG AND c.TABLE_SCHEMA = k.TABLE_SCHEMA AND c.TABLE_NAME = k.TABLE_NAME 
					AND c.COLUMN_NAME = k.COLUMN_NAME
				WHERE c.TABLE_NAME = '$object[foreign_table]' AND c.ORDINAL_POSITION = 2 $where_string
				ORDER BY database_name, schema_name, table_name, position
				");
			
			$foreign_object = mssql_fetch_assoc($result);
			mssql_free_result($result);
			
			$foreign_object[original_data_type] = $foreign_object[data_type];
			$foreign_object[data_type] = $this->DataTypeMap($foreign_object[data_type]);
			
			$foreign_object[table_name] = $object[foreign_table];

			//adds the foreign element into the objects' array
			array_push(&$objects, $foreign_object);
		}
		
		/**
		 * Gets DB tables list
		 *
		 * @return array
		 */
		public function GetTables(array $options)
		{
			$where_string = '';

			if (isset($options[database])) $where_string .= "AND c.TABLE_CATALOG = '$options[database]'";
			if (isset($options[schema])) $where_string .= "AND c.TABLE_SCHEMA = '$options[schema]'";
						
			$result = mssql_query("
				SELECT t.TABLE_NAME as table_id, t.TABLE_CATALOG + '.' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME as table_name
				FROM INFORMATION_SCHEMA.TABLES t
				WHERE 1=1 $where_string
				ORDER BY table_name;
				");

			$objects = array();
			while ($object = mssql_fetch_assoc($result))
			{
				$objects[] = $object;
			}
			mssql_next_result($result);
			mssql_free_result($result);
			$this->UseCurrentDatabase();

			return $objects;
		}
		
		/**
		 * Gets SQL object code
		 *
		 * @param string $obj_name name of the object
		 * @return array SQL-code of the database objectsn by the line
		 */
		public function GetSqlObjectCode($obj_name)
		{
			$result = mssql_query(" EXEC sp_helptext $obj_name");
			while ($object = mssql_fetch_assoc($result))
			{
				$objects[] = $object["Text"];
			}
			return $objects;
		}
		
		/**
		 * Gets SQL object names
		 *
		 * @param string $obj_name part of the name of the SQL-object to pick out only necessary objects
		 * @return array SQL-object names of the database
		 */
		public function GetSqlObjectNames($obj_name = '')
		{
			$result = mssql_query(" EXEC sp_help");
			while ($object = mssql_fetch_assoc($result))
			{
				$objects[] = $object;
			}
			foreach ($objects as $value)
			{
				if (strpos($value[Name], $obj_name) !== false)	
				{
					$object_names[] = $value[Name];
				}
			}
			return $object_names;
		}
		
		/**
		 * Maps database datatypes to application datatypes
		 *
		 * @param string $type data type
		 * @return string mapped datatype
		 */
		public function DataTypeMap($type)
		{
			switch($type)
			{
				case 'varchar': case 'char':
					return 'varchar'; break;
				case 'tinytext': case 'text': case 'mediumtext': case 'longtext':
					return 'text'; break;
				case 'integer': case 'bigint': case 'int': case 'tinyint': case 'longint': case 'mediumint': case 'smallint': 
					return 'bigint'; break;
				case 'decimal':
					return 'decimal'; break;
				case 'bit':
					return 'bit'; break;
				case 'date':
					return 'date'; break;
				case 'datetime':
					return 'datetime'; break;
			}
		}
		
		/**
		 * Parses object constraints
		 *
		 */
		public function ParseConstraints(&$object)
		{
			$object[constraint_name] = ereg_replace('[0-9]', '', $object[constraint_name]);
			$object[primary_key] = ereg("^(PK_)", $object[constraint_name]) ? true : false;
			$object[foreign_table] = (ereg('^(FK_)', $object[constraint_name])) ? ereg_replace("^FK_$object[table_name]_", '', $object[constraint_name]) : '';			
		}
		
		
		//PARAMETERS FUNCTIONS
		
		/**
 		* Binds parameters from array to the stored procedure
 		* 
 		* @param string $parameters parameters' array
 		* @param string $proc_name stored_procedure name
 		* @param array $db_structure table(s)' structure
 		*/
		function BindParameters($parameters, $proc_name, $db_structure)
		{
			if (isset($parameters))
			{
				foreach ($parameters as $key=>$value)
				{
					if ($key != NULL)
					{
						if ($key =="sort_by")
						{
							$this->SanitizeParameters($key, &$value);
							if ($value==null) 
							{
								unset($params[$key]); 
								continue;
							}
							if (empty($db_structure[$value]['table_name']))
							{
								global $current_controller;
								$value = $current_controller->controller_model->table_name.".".$db_structure[$value]['name'];							
								unset($current_controller);
							}
							else 
							{
								$value = $db_structure[$value]['table_name'].".".$db_structure[$value]['name'];
							}
							
							mssql_bind($proc_name, '@'.$key, $value, SQLVARCHAR);
						}
						else if (($key=="sort_method")||($key=="page_offset")||($key=="page_number"))
						{
							$this->SanitizeParameters($key, &$value);
							
							if ($value==null) 
							{
								unset($params[$key]); 
								continue;
							}
							mssql_bind($proc_name, '@'.$key, $value, SQLVARCHAR);
						}
						else
						{
							if ($this->InDBStructure($key, $db_structure))
							{
								if ($this->GetParameterType($key, $db_structure) == 'text' || $this->GetParameterType($key, $db_structure) == 'file')
								{
									$res = mssql_bind($proc_name, '@'.$key, $value, SQLTEXT);	
									$r = 5;
								}
								else 
								{
								    if (($this->GetParameterType($key, $db_structure) == 'bit') && ($value == '')) $value = '0';
									$res = mssql_bind($proc_name, '@'.$key, $value, SQLVARCHAR);	
									$r = 5;
								}
							}
						}
					}
				}
			}
		}
		
		/**
		 * Stops SQL injections
		 *
		 * @param string $key field name
		 * @param string $value field value
		 */
		function SanitizeParameters($key, &$value)
		{
			switch ($key)
			{
				case "sort_method":
					if (($value != 'asc') && ($value != 'desc')) $value = '';
					break;
					
				case "page_offset":
				case "page_number":
					if (!is_numeric($value)) $value = '';
					break;
					
				case "sort_by":
					if (!eregi("(a-zA-Z0-9\._)*", $value)) $value = '';
					break;
			}
		}
		
		/**
 		* Checks if given name is in $db_structure
 		* 
 		* @param string $name name of the element
 		* @param array $db_structure table(s)' structure
 		* @return boolean $result true if found, otherwise - false
 		*/
		function InDBStructure($name, $db_structure)
		{
			$result = false;
			foreach ($db_structure as $key=>$value)
			{
				if ($name == $value["name"])
				{
					$result = true;
					break;
				}
			}
			return $result;
		}
		
		/**
 		* Gets parameter type by name
 		* 
 		* @param string $parameter_name: name of the parameter
 		* @param array $db_structure table(s)' structure
 		* @return string $returnValue parameter type or null if not found
 		*/
		function GetParameterType($parameter_name, $db_structure)
		{
			$returnValue = null;
			foreach ($db_structure as $key=>$value)
			{
				if ($value['name'] == $parameter_name)
				{
					$returnValue = $value['type'];
					break;
				}
			}
			return $returnValue;
		}
		
		
		//FUNCTIONS FOR STORED PROCEDURES
		
		/**
 		* Executes stored procedure
 		* 
 		* @param string $proc_name stored procedure name
 		* @param array $params parameters' array
 		* @param array $db_structure db_structure array from the model
   		* @param array $out_parameters out parameters
  		* @return objects $objects DB query result(s)
 		*/
		function ExecuteStoredProcedure($proc_name, $params, $db_structure, &$out_parameters = null)
		{
			Logger::Debug("$proc_name started");

			$sp = mssql_init($proc_name, $this->connection);
			if (!(empty($params)))
			{
				$this->BindParameters($params, $sp, $db_structure);
			}
			if (isset($out_parameters))
			{
				foreach ($out_parameters as $key=>$value)
				{
					mssql_bind($sp, '@'.$value, &$$value, SQLVARCHAR, true);
					$out_params[$value] = $$value;
				}
			}
			
			$this->ExecuteSQL("SET DATEFORMAT dmY");
			$result = mssql_execute($sp);

			if ($result == "true")
			{
				if (isset($out_parameters))
				{
					foreach ($out_parameters as $key=>$val)
					{
						$out_params[$val] =$$val; 
					}
				}
				$out_parameters = $out_params;
				$this->UseCurrentDatabase();
				return true;
			}
			else 
			{
				$objects = array();
				while ($object = mssql_fetch_assoc($result))
				{
					$objects[] = $object;
				}
				mssql_next_result($result);
				if (isset($out_parameters))
				{				
					foreach ($out_parameters as $key=>$val)
					{
						$out_params[$val] = $$val; 
					}
				}
				mssql_free_result($result);
				$out_parameters = $out_params;
				$this->UseCurrentDatabase();
				
				Logger::Debug("$proc_name ended");
				
				return $objects;
			}
		}	
				
		/**
 		* Executes stored procedure and returns a single object
 		* 
 		* @param string $proc_name stored procedure name
 		* @param array $params parameters' array
 		* @param array $db_structure db_structure array from the model
 		* @return object $object DB query result
 		*/
		function ExecuteStoredProcedureObject($proc_name, $params=null, $db_structure)
		{
			Logger::Debug("$proc_name started");
			
			$sp = mssql_init($proc_name, $this->connection);			
			$this->BindParameters($params, $sp, $db_structure);
			$result = mssql_execute($sp);
			$object = mssql_fetch_assoc($result);
			mssql_free_result($result);
			$this->UseCurrentDatabase();
			
			Logger::Debug("$proc_name ended");
			
			return $object;
		}
		
		/**
 		* Execute stored procedure and returns a list of objects
 		* 
 		* @param string $proc_name stored procedure name
 		* @param array $params parameters' array
 		* @param array $db_structure db_structure array from the model
   		* @param array $out_parameters out parameters
  		* @return objects $objects DB query results
  		*/
		function ExecuteStoredProcedureObjects($proc_name, $params=null, $db_structure, $out_parameters=null)
		{
			Logger::Debug("$proc_name started");
			$sp = mssql_init($proc_name, $this->connection);
			$this->BindParameters($params, $sp, $db_structure);
			if (isset($out_parameters))
			{			
				foreach ($out_parameters as $key=>$value)
				{
					mssql_bind($sp, '@'.$value, &$$value, SQLVARCHAR, true);
					$out_params[$value] = $$value;
				}
			}
			
			$result = mssql_execute($sp);
			
			$objects = array();
			while ($object = mssql_fetch_assoc($result))
			{
				$objects[] = $object;
			}
			mssql_next_result($result);
			if (isset($out_parameters))
			{			
				foreach ($out_parameters as $key=>$val)
				{
					$out_params[$val] =$$val; 
				}
			}
			mssql_free_result($result);
			$out_parameters = $out_params;
			$this->UseCurrentDatabase();
			
			Logger::Debug("$proc_name ended");

			return $objects;
		}
		
		/**
 		* Changes database to the database from Config object
  		*/
		function UseCurrentDatabase()
		{
			global $object;
			if ($object->config->use_several_databases)
			{
				global $current_controller;
				$controller_model = $current_controller->controller_model;
				$database_config_name = $controller_model->database_config;
				$array = $controller_model->config->$database_config_name;
				$database_name = $array[database];
				$sql = "USE ".$database_name;
				$this->ExecuteSQL($sql);
			}
		}
		
		
		//FUNCTIONS FOR NON-STORED PROCEDURES
		
		/**
 		* Execute stored procedure and returns a list of objects
 		* 
 		* @param string $sql sql query
  		* @return objects $objects DB query results
  		*/
		function ExecuteSQL($sql)
		{
			Logger::Debug("SQL ($sql) executed");
			$result = mssql_query($sql);
			$objects = array();
			
			if ($this->IsMssqlResource($result))
			{
				while ($object = mssql_fetch_assoc($result))
				{
					$objects[] = $object;
				}
			}
			
			return $objects;
		}
		
		/**
		 * Returns true if $value is a mssql resource. Otherwise - false
		 *
		 * @param object $value value to check
		 * @return true/false
		 */
		function IsMssqlResource($value)
		{
			if (($value == 'true') || ($value == 'false')) 
				return false;
			else
				return true;
		}
		
	}
?>